package admin.util;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;

import admin.DTO.ComponentDTO;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ExcelUtil {
	public static WritableSheet excelTitle(int column, int record, String name,WritableSheet sheet) throws IOException, WriteException{
		WritableCellFormat titleFormat = new WritableCellFormat();
		titleFormat.setAlignment(Alignment.CENTRE);// 셀 가로정렬
		titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE); //셀 세로 정렬
		titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
		titleFormat.setWrap(false);
		WritableCell cell = new Label(column,record,name);
		sheet.addCell(cell);
		return sheet;
	}
	
	public static WritableSheet excelCell(int column,int i,String list,WritableSheet sheet) throws RowsExceededException, WriteException{
		WritableCell temp = new Label(column,i,list);
		sheet.addCell(temp);
		return sheet;
	}
	
	public  void excel1(ArrayList<ComponentDTO> list) throws IOException, WriteException{
		long name = System.currentTimeMillis();
		File downFile = new File("c:\\mes\\excel\\"+"faultN."+name+".xls");
		WritableWorkbook workbook = Workbook.createWorkbook(downFile);
		WritableSheet sheet = workbook.createSheet("부품 불량", 0);
		sheet = ExcelUtil.excelTitle(0, 0, "부품아이디", sheet);
		sheet = ExcelUtil.excelTitle(1, 0, "부품이름", sheet);
		sheet = ExcelUtil.excelTitle(2, 0, "부품불량여부", sheet);
		for(int i=1; i<list.size()+1; i++){
			sheet = ExcelUtil.excelCell(0, i, String.valueOf(list.get(i-1).getComponent_id()), sheet);
			sheet = ExcelUtil.excelCell(1, i, list.get(i-1).getComponent_name(), sheet);
			sheet = ExcelUtil.excelCell(2, i, list.get(i-1).getIs_fault(), sheet);
		}
		File imgFile = new File("C:\\MES\\img\\" + "K0001.png");
		WritableImage img = new WritableImage(0,list.size()+1,10,15,imgFile);
		sheet.addImage(img);
		workbook.write();
		workbook.close();
	}
	
	public  void excel2(ArrayList<ComponentDTO> list) throws IOException, WriteException{
		long name = System.currentTimeMillis();
		File downFile = new File("c:\\mes\\excel\\"+"faultY."+name+".xls");
		WritableWorkbook workbook = Workbook.createWorkbook(downFile);
		WritableSheet sheet = workbook.createSheet("부품 불량", 0);
		sheet = ExcelUtil.excelTitle(0, 0, "부품아이디", sheet);
		sheet = ExcelUtil.excelTitle(1, 0, "부품이름", sheet);
		sheet = ExcelUtil.excelTitle(2, 0, "부품불량여부", sheet);
		for(int i=1; i<list.size()+1; i++){
			sheet = ExcelUtil.excelCell(0, i, String.valueOf(list.get(i-1).getComponent_id()), sheet);
			sheet = ExcelUtil.excelCell(1, i, list.get(i-1).getComponent_name(), sheet);
			sheet = ExcelUtil.excelCell(2, i, list.get(i-1).getIs_fault(), sheet);
		}
		File imgFile = new File("C:\\MES\\img\\" + "K0002.png");
		WritableImage img = new WritableImage(0,list.size()+1,10,15,imgFile);
		sheet.addImage(img);
		workbook.write();
		workbook.close();
	}
	
	public  void excel3(ArrayList<ComponentDTO> list) throws IOException, WriteException{
		long name = System.currentTimeMillis();
		File downFile = new File("c:\\mes\\excel\\"+"faultALL."+name+".xls");
		WritableWorkbook workbook = Workbook.createWorkbook(downFile);
		WritableSheet sheet = workbook.createSheet("부품 불량", 0);
		sheet = ExcelUtil.excelTitle(0, 0, "부품아이디", sheet);
		sheet = ExcelUtil.excelTitle(1, 0, "부품이름", sheet);
		sheet = ExcelUtil.excelTitle(2, 0, "부품불량여부", sheet);
		for(int i=1; i<list.size()+1; i++){
			sheet = ExcelUtil.excelCell(0, i, String.valueOf(list.get(i-1).getComponent_id()), sheet);
			sheet = ExcelUtil.excelCell(1, i, list.get(i-1).getComponent_name(), sheet);
			sheet = ExcelUtil.excelCell(2, i, list.get(i-1).getIs_fault(), sheet);
		}
		File imgFile = new File("C:\\MES\\img\\" + "K0003.png");
		WritableImage img = new WritableImage(0,list.size()+1,10,15,imgFile);
		sheet.addImage(img);
		workbook.write();
		workbook.close();
	}
}
